package com.youlai.boot.util.lwb;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Consumer;

public class TestExcel {
    public static void main(String[] args) {
        try {
            String filePath = "E://项目数据导入模版_1.xls";
            ExcelReader excelReader = ExcelUtil.getReader(filePath);
            ExcelReader setSheet1 = excelReader.setSheet(0);
            List<List<Object>> sheet1Data = setSheet1.read(1);
            if (sheet1Data.size() < 2) {
                System.out.println("数据行数不足");
                return;
            }

            ProjectData projectData = new ProjectData();
            Map<Integer, Consumer<String>> fieldSetters = new HashMap<>();
            fieldSetters.put(1, projectData::setProjectName);
            fieldSetters.put(2, projectData::setBranchCompany);
            fieldSetters.put(3, projectData::setSeaArea);
            fieldSetters.put(4, projectData::setGeographicalLocation);
            fieldSetters.put(5, projectData::setTectonicUnit);
            fieldSetters.put(6, projectData::setReservoirType);
            // 特殊处理Integer字段
            fieldSetters.put(7, str -> {
                try {
                    if (StrUtil.isNotBlank(str)) {
                        Integer averageWaterDepth = Integer.valueOf(str);
                        projectData.setAverageWaterDepth(averageWaterDepth);
                    }
                } catch (Exception e) {
                    System.err.println("序号7[平均水深]格式错误: " + str);
                }
            });
            fieldSetters.put(8, projectData::setRelatedUnits);
            fieldSetters.put(9, projectData::setConstructionPeriod);
            // 特殊处理LocalDate字段
            fieldSetters.put(10, str -> {
                try {
                    if (StrUtil.isNotBlank(str)) {
                        LocalDateTime date = LocalDateTime.parse(str, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                        projectData.setCommissioningTime(date.toLocalDate());
                    }
                } catch (Exception e) {
                    System.err.println("序号10[投产时间]:日期格式错误: " + str);
                }
            });
            fieldSetters.put(11, projectData::setEvaluationScope);
            // 特殊处理LocalDate字段
            fieldSetters.put(12, str -> {
                try {
                    if (StrUtil.isNotBlank(str)) {
                        LocalDateTime date = LocalDateTime.parse(str, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
                        projectData.setEvaluationPoint(date.toLocalDate());
                    }
                } catch (Exception e) {
                    System.err.println("序号12[后评价时点]:日期格式错误: " + str);
                }
            });

            for (int i = 1; i < sheet1Data.size() && i <= fieldSetters.size(); i++) {
                List<Object> row = sheet1Data.get(i);
                if (CollUtil.isEmpty(row) || row.size() <= 2) {
                    continue;
                }

                Object value = row.get(2);
                String strValue = value == null ? "" : value.toString();
                Consumer<String> setter = fieldSetters.get(i);
                if (setter != null) {
                    setter.accept(strValue);
                }
            }

            //System.out.println(projectData);
            ArrayList<String> indexList = new ArrayList<>();
            indexList.add("7");
            //读取sheet2的数据
            ExcelReader setSheet2 = excelReader.setSheet(0);
            List<List<Object>> sheet2Data = setSheet2.read(2);
            for (List<Object> sheet2Datum : sheet2Data) {
                Object index = sheet2Datum.get(0);
                Object name = sheet2Datum.get(1);
                Object names = sheet2Datum.get(2);
                String pinyin = PinyinUtil.getFirstLetter(name.toString(), "");


                String filed = pinyin + index;
                if (indexList.contains(index.toString())){
                    String sqlInt = "\"%s\" decimal(15,2) DEFAULT '0.00' COMMENT '%s',";
                    String format = String.format(sqlInt, filed, name);
                    System.out.println(format);
                }else {
                    String sql = "\"%s\" varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '%s',";
                    String format = String.format(sql, filed, name);
                    System.out.println(format);
                }
            }

//            System.out.println(sheet2Data);

        } catch (Exception e) {
            System.err.println("处理Excel文件出错: " + e.getMessage());
        }
    }
}
